package com.dhl.fin.api.controller;

import com.dhl.fin.api.common.controller.CommonController;
import com.dhl.fin.api.common.dto.ApiResponse;
import com.dhl.fin.api.common.service.RedisService;
import com.dhl.fin.api.common.util.ArrayUtil;
import com.dhl.fin.api.common.util.DateUtil;
import com.dhl.fin.api.common.util.MapUtil;
import com.dhl.fin.api.common.util.WebUtil;
import com.dhl.fin.api.common.util.excel.ExcelUtil;
import com.dhl.fin.api.domain.Stock;
import com.dhl.fin.api.domain.StockV;
import com.dhl.fin.api.service.LineChartDataServicelmpl;
import com.dhl.fin.api.service.StockServiceImpl;
import com.dhl.fin.api.service.StockVServiceImpl;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import java.io.File;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Created by CuiJianbo on 2020.05.17.
 */
@Transactional(rollbackFor = Exception.class)
@RestController
@RequestMapping("stock")
public class StockController extends CommonController<Stock> {

    Integer startYear = 1995;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private StockVServiceImpl stockVService;

    @Autowired
    private RedisService redisService;


    @Autowired
    private LineChartDataServicelmpl lineChartDataServicelmpl;

    @Autowired
    private StockServiceImpl stockService;

    @RequestMapping("/uploadv")
    public ApiResponse uploadv() throws Exception {

        File dir = new File("C:\\Beck_Personal\\stock\\stock excel\\xlsx");
        File[] files = dir.listFiles();
        for (File p : files) {
            if (p.getName().endsWith("xlsx")) {
                String year = p.getName().substring(0, 4);
//                if (!year.equalsIgnoreCase("2019")) {
//                    continue;
//                }
                List<StockV> stocks = ExcelUtil.getExcelFromLocal(p, StockV.class);
                for (StockV stock : stocks) {

                    Double totalMV = stock.getTotalMarketValue();
                    Double totalStock = stock.getTotalStock();
                    Double eps = stock.getEps();
                    if (totalMV != null && totalStock != null && eps != null && totalMV > 0 && totalStock > 0 && eps > 0) {
                        Double pe = (totalMV / totalStock) / eps;
                        pe = new BigDecimal(pe).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        stock.setPe(pe);
                    }
                    String code = stock.getCode();
                    if (!code.startsWith("A")) {
                        stock.setCode(String.format("%06d", Integer.valueOf(code)));
                    } else {
                        continue;
                    }
                    stock.setYear(Integer.valueOf(year));
                    stockVService.save(stock);
                }
            }

        }

        return ApiResponse.success();
    }

    @RequestMapping("/upload")
    public ApiResponse upload(MultipartHttpServletRequest request) {
     /*   List<Stock> stocks = ExcelUtil.getExcelFromRequest(request, Stock.class);
        Date date = WebUtil.getDateParam("date");
        try {
            for (Stock stock : stocks) {
                stock.setDate(date);
                commonService.saveOrUpdate(stock);
            }
        } catch (Exception e) {
            e.printStackTrace();
            ApiResponse.error("导入失败");
        }
*/
        return ApiResponse.success();
    }

    /*    股票代码     股票简称    总市值       总股本     流通A股占总股本比例      市净率    市盈率     A股市值    每股收益EPS-基本       每股净资产BPS      销售净利率
        stock_code    name    total_value   total_pes       a_in_total                pb        pe       a_value         eps                    bps         net_profit_ratio

        销售毛利率           营业成本率        净利润/营业总收入    营业利润/营业总收入   营业费用/营业总收入     管理费用/营业总收入     财务费用/营业总收入
    gross_profit_ratio  oper_cost_ratio      pure_profit_in_total  net_profit_in_total     pro_cost_in_total       mg_cost_in_total        fin_cost_in_total

       资产负债率   营业总收入(同比增长率)   营业收入(同比增长率)  利润总额(同比增长率)   单季度：净利润环比增长率   单季度：净利润同比增长率     单季度：营业收入环比增长率    每10股股利（税前）
          loar       total_income_rise_yoy     income_rise_yoy        profit_rise_yoy          pure_profit_mom             pure_profit_yoy            income_rise_mom               dividend

    */
    @RequestMapping("/uploadstock")
    public ApiResponse uploadStock() {
        File files = new File("C:\\Beck_Personal\\stock\\A股xlsx");
        if (files.isDirectory()) {
            Arrays.stream(files.listFiles()).forEach(p -> {
                String year = p.getName();
                if (p.isDirectory()) {
                    Arrays.stream(p.listFiles()).forEach(file -> {
                        if (!file.isDirectory() && file.getName().endsWith("xlsx")) {
                            String quarter = file.getName().substring(0, 1);
                            List<Map> stocks = ExcelUtil.getExcelFromLocal(file.getAbsolutePath());
                        }
                    });
                }
            });
        }


        return ApiResponse.success();
    }

    private void importStock(File file, String year, String quarter) {
        List<Map> stocks = ExcelUtil.getExcelFromLocal(file.getAbsolutePath());
        for (Map stock : stocks) {
            String stockCode = MapUtils.getString(stock, "股票代码");
            stockCode = StringUtils.isEmpty(stockCode) || stockCode.equalsIgnoreCase("-") || stockCode.equalsIgnoreCase("-") ? "0" : stockCode;
            String name = MapUtils.getString(stock, "股票简称");
            name = StringUtils.isEmpty(name) || name.equalsIgnoreCase("-") ? "0" : name;
            String totalValue = MapUtils.getString(stock, "总市值");
            totalValue = StringUtils.isEmpty(totalValue) || totalValue.equalsIgnoreCase("-") ? "0" : totalValue;
            String totalPes = MapUtils.getString(stock, "总股本");
            totalPes = StringUtils.isEmpty(totalPes) || totalPes.equalsIgnoreCase("-") ? "0" : totalPes;
            String aInTotal = MapUtils.getString(stock, "流通A股占总股本比例");
            aInTotal = StringUtils.isEmpty(aInTotal) || aInTotal.equalsIgnoreCase("-") ? "0" : aInTotal;
            String pb = MapUtils.getString(stock, "市净率");
            pb = StringUtils.isEmpty(pb) || pb.equalsIgnoreCase("-") ? "0" : pb;
            String avalue = MapUtils.getString(stock, "A股市值");
            avalue = StringUtils.isEmpty(avalue) || avalue.equalsIgnoreCase("-") ? "0" : avalue;
            String eps = MapUtils.getString(stock, "每股收益EPS-基本");
            eps = StringUtils.isEmpty(eps) || eps.equalsIgnoreCase("-") ? "0" : eps;
            String bps = MapUtils.getString(stock, "每股净资产BPS");
            bps = StringUtils.isEmpty(bps) || bps.equalsIgnoreCase("-") ? "0" : bps;
            String netProfitRatio = MapUtils.getString(stock, "销售净利率");
            netProfitRatio = StringUtils.isEmpty(netProfitRatio) || netProfitRatio.equalsIgnoreCase("-") ? "0" : netProfitRatio;
            String grossProfitRatio = MapUtils.getString(stock, "销售毛利率");
            grossProfitRatio = StringUtils.isEmpty(grossProfitRatio) || grossProfitRatio.equalsIgnoreCase("-") ? "0" : grossProfitRatio;
            String operCostRatio = MapUtils.getString(stock, "营业成本率");
            operCostRatio = StringUtils.isEmpty(operCostRatio) || operCostRatio.equalsIgnoreCase("-") ? "0" : operCostRatio;
            String pureProfitInTotal = MapUtils.getString(stock, "净利润/营业总收入");
            pureProfitInTotal = StringUtils.isEmpty(pureProfitInTotal) || pureProfitInTotal.equalsIgnoreCase("-") ? "0" : pureProfitInTotal;
            String netProfitInTotal = MapUtils.getString(stock, "营业利润/营业总收入");
            netProfitInTotal = StringUtils.isEmpty(netProfitInTotal) || netProfitInTotal.equalsIgnoreCase("-") ? "0" : netProfitInTotal;
            String proCostInTotal = MapUtils.getString(stock, "营业费用/营业总收入");
            proCostInTotal = StringUtils.isEmpty(proCostInTotal) || proCostInTotal.equalsIgnoreCase("-") ? "0" : proCostInTotal;
            String mgCostInTotal = MapUtils.getString(stock, "管理费用/营业总收入");
            mgCostInTotal = StringUtils.isEmpty(mgCostInTotal) || mgCostInTotal.equalsIgnoreCase("-") ? "0" : mgCostInTotal;
            String finCostInTotal = MapUtils.getString(stock, "财务费用/营业总收入");
            finCostInTotal = StringUtils.isEmpty(finCostInTotal) || finCostInTotal.equalsIgnoreCase("-") ? "0" : finCostInTotal;
            String loar = MapUtils.getString(stock, "资产负债率");
            loar = StringUtils.isEmpty(loar) || loar.equalsIgnoreCase("-") ? "0" : loar;
            String totalIncomeRiseYoy = MapUtils.getString(stock, "营业总收入(同比增长率)");
            totalIncomeRiseYoy = StringUtils.isEmpty(totalIncomeRiseYoy) || totalIncomeRiseYoy.equalsIgnoreCase("-") ? "0" : totalIncomeRiseYoy;
            String incomeRiseYoy = MapUtils.getString(stock, "营业收入(同比增长率)");
            incomeRiseYoy = StringUtils.isEmpty(incomeRiseYoy) || incomeRiseYoy.equalsIgnoreCase("-") ? "0" : incomeRiseYoy;
            String profitRiseYoy = MapUtils.getString(stock, "利润总额(同比增长率)");
            profitRiseYoy = StringUtils.isEmpty(profitRiseYoy) || profitRiseYoy.equalsIgnoreCase("-") ? "0" : profitRiseYoy;
            String pureProfitMom = MapUtils.getString(stock, "单季度：净利润环比增长率");
            pureProfitMom = StringUtils.isEmpty(pureProfitMom) || pureProfitMom.equalsIgnoreCase("-") ? "0" : pureProfitMom;
            String pureProfitYoy = MapUtils.getString(stock, "单季度：净利润同比增长率");
            pureProfitYoy = StringUtils.isEmpty(pureProfitYoy) || pureProfitYoy.equalsIgnoreCase("-") ? "0" : pureProfitYoy;
            String incomeRiseMom = MapUtils.getString(stock, "单季度：营业收入环比增长率");
            incomeRiseMom = StringUtils.isEmpty(incomeRiseMom) || incomeRiseMom.equalsIgnoreCase("-") ? "0" : incomeRiseMom;
            String dividend = MapUtils.getString(stock, "每10股股利（税前）");
            dividend = StringUtils.isEmpty(dividend) || dividend.equalsIgnoreCase("-") ? "0" : dividend;
            String sql = String.format("insert into " + "t_stock_value(stock_code,name,total_value,total_pes,a_in_total,pb,a_value,eps,bps,net_profit_ratio," + "gross_profit_ratio,oper_cost_ratio,pure_profit_in_total,net_profit_in_total,pro_cost_in_total,mg_cost_in_total,fin_cost_in_total,loar,total_income_rise_yoy,income_rise_yoy," + "profit_rise_yoy,pure_profit_mom,pure_profit_yoy,income_rise_mom,dividend,year,quarter,create_time)" + "values('%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,now())", stockCode, name, totalValue, totalPes, aInTotal, pb, avalue, eps, bps, netProfitRatio, grossProfitRatio, operCostRatio, pureProfitInTotal, netProfitInTotal, proCostInTotal, mgCostInTotal, finCostInTotal, loar, totalIncomeRiseYoy, incomeRiseYoy, profitRiseYoy, pureProfitMom, pureProfitYoy, incomeRiseMom, dividend, year, quarter);
            System.out.println(sql);
            jdbcTemplate.execute(sql);
        }

    }

    @RequestMapping("/stockyearpe/{code}")
    public ApiResponse getStockYearPE(@PathVariable String code) {

        return ApiResponse.success();
    }

    @RequestMapping("/allstock")
    public ApiResponse getAllStocks() {


        List<Map> allStocks = jdbcTemplate.queryForList("select * from t_stock_company").stream().map(p -> MapUtil.builder().add("name", MapUtils.getString(p, "code") + "-" + MapUtils.getString(p, "name")).add("value", MapUtils.getString(p, "code")).build()).collect(Collectors.toList());

        List<Double> allCompanyPEyears = jdbcTemplate.queryForList("select year, ceil(avg(pe)) pe from v_stock where exchange_company is not null and pe is not null group by year").stream().map(p -> Math.ceil(MapUtils.getDouble(p, "pe"))).collect(Collectors.toList());

        List years = allYear();


        Map result = MapUtil.builder().add("all", allStocks).add("years", years).add("allcompany", allCompanyPEyears).build();


        return ApiResponse.success(result);

    }

    @RequestMapping("/stock/{code}")
    public ApiResponse getStock(@PathVariable String code) throws Exception {

        Map data = jdbcTemplate.queryForMap("select  code, trade,type,name ,exchange_company from v_stock where code ='" + code + "' limit 0,1");
        String name = MapUtil.getString(data, "name");
        String trade = MapUtil.getString(data, "trade");
        String type = MapUtil.getString(data, "type");
        String company = MapUtil.getString(data, "exchange_company");
        List<String> allYear = allYear();


        List<Map<String, Object>> tempTradePE = jdbcTemplate.queryForList("select year, ceil(avg(pe)) pe from v_stock where exchange_company is not null and pe is not null and trade = '" + trade + "' group by year");
        List<String> tradePEyears = new LinkedList<>();
        for (String s : allYear) {
            boolean flag = false;
            for (Map<String, Object> stringObjectMap : tempTradePE) {
                if (stringObjectMap.get("year").toString().equals(s)) {
                    flag = true;
                    tradePEyears.add(stringObjectMap.get("pe").toString());
                }
            }
            if (!flag) {
                tradePEyears.add("0");
            }
        }


        List<String> typePEyears = jdbcTemplate.queryForList("select year, ceil(avg(pe)) pe from v_stock where exchange_company is not null and pe is not null and type = '" + type + "' group by year").stream().map(p -> MapUtils.getString(p, "pe")).collect(Collectors.toList());

        List<Map<String, Object>> tempCodePE = jdbcTemplate.queryForList("select year, ceil(avg(pe)) pe from v_stock where exchange_company is not null and pe is not null and code = '" + code + "' group by year");
        List<String> stockPEyears = new LinkedList<>();

        for (String s : allYear) {
            boolean flag = false;
            for (Map<String, Object> item : tempCodePE) {
                if (item.get("year").toString().equals(s)) {
                    flag = true;
                    stockPEyears.add(item.get("pe").toString());
                    break;
                }
            }
            if (!flag) {
                stockPEyears.add("0");
            }
        }

        Map result = MapUtil.builder().add("typePE", typePEyears).add("tradePE", tradePEyears).add("stockPE", stockPEyears).add("trade", trade).add("stock", name).add("company", company.substring(0, 1) + type.substring(0, 1)).build();

        return ApiResponse.success(result);
    }

    public List<String> allYear() {
        Integer year = Calendar.getInstance().get(Calendar.YEAR);
        List<String> years = new LinkedList<>();
        for (Integer i = startYear; i < year; i++) {
            years.add(i.toString());
        }
        return years;
    }

    @RequestMapping("/class/{code}")
    public ApiResponse getStockClass(@PathVariable String code) throws Exception {

        List<Map<String, Object>> datas = jdbcTemplate.queryForList("select " + code + ", ceil(avg(pe)) pe from v_stock where exchange_company is not null and year='2019' and pe is not null  group by " + code + " order by avg(pe) desc");
        List<String> trades = new LinkedList<>();
        List<String> pe = new LinkedList<>();
        for (Map<String, Object> data : datas) {
            if (data.get(code) == null) {
                continue;
            }
            trades.add(data.get(code) == null ? "0" : data.get(code).toString());
            pe.add(data.get("pe").toString());
        }
        return ApiResponse.success(MapUtil.builder().add("value", pe).add("trades", trades).build());
    }

    @RequestMapping("/getline")
    public ApiResponse getLineChart() throws Exception {
        String[] basicAspect = WebUtil.getStringArrayParam("basicAspect");
        if (ArrayUtil.isEmpty(basicAspect)) {
            return ApiResponse.success();
        }

        List data = lineChartDataServicelmpl.getLineData(basicAspect);
        return ApiResponse.success(data);

    }

    @RequestMapping("/getaspect")
    public ApiResponse getAspect() throws Exception {
        Map dd = redisService.getDictionary("basicAspect");
        List<Map> data = new LinkedList<>();
        for (Object o : dd.keySet()) {
            data.add(MapUtil.builder().add("name", dd.get(o).toString()).add("value", o.toString()).build());
        }
        return ApiResponse.success(data);

    }

    @RequestMapping("/gettradeline")
    public ApiResponse getTradeLine() throws Exception {
        List<Map> data = lineChartDataServicelmpl.getTradeLine();
        return ApiResponse.success(data);

    }

    @RequestMapping("/addlinedata")
    public ApiResponse addLineData() throws Exception {

        String amount1 = WebUtil.getStringParam("amount1");
        String amount2 = WebUtil.getStringParam("amount2");
        String type = WebUtil.getStringParam("type");
        Date pointDate = DateUtil.getDate(WebUtil.getStringParam("pointDate"));

        lineChartDataServicelmpl.addLineData(amount1, amount2, type, pointDate);

        return ApiResponse.success();

    }

    @RequestMapping("/totalvalue")
    public ApiResponse totalValue() {

        Map result = stockService.totalMarketValue();
        return ApiResponse.success(result);

    }


    /**
     * 刷新总市值表数据t_stock_total_market_value
     * @return
     */
    @RequestMapping("/freshtotal")
    public ApiResponse freshTotalMarketValue() {

        stockService.freshTotalMarketValue();

        return ApiResponse.success();

    }


}



